import itertools
import pymysql
import json
import os

def str2(i):
    return format(i, '02d')


dict_items=None
def get_ssq_all():   
    global dict_items
    if os.path.exists('./ssq_all.json') and dict_items==None:
        with open('./ssq_all.json') as file:
            dict_items=json.load(file)
        return dict_items
    # 生成红色球号码的范围（1-33）
    red_balls = list(range(1, 34))

    # 生成蓝色球号码的范围（1-16）
    blue_balls = list(range(1, 17))

    # 使用itertools模块的combinations函数生成所有可能的红色球组合
    red_combinations = list(itertools.combinations(red_balls, 6))

    # 生成所有红蓝球号码的组合
    all_combinations = []
    for red_combination in red_combinations:
        for blue_ball in blue_balls:
            all_combinations.append(red_combination + (blue_ball,))

    # # 打印所有双色球号码的组合
    # for i, combination in enumerate(all_combinations, 1):
    #     print(f"Combination {i}: {combination}")

    # 打印所有双色球号码的组合
    
    for i, combination in enumerate(all_combinations, 1): 
        # print(f"Combination {i}: {combination}")   
        str_ssq=','.join(map(str2, combination))
        dict_items[str_ssq]=i-1
        # print(f"Combination {i}: {str_ssq}")
        # if i>30:
        #     break
    with open('./ssq_all.json','w') as file:
        json.dump(dict_items, file)
    return dict_items

def get_windex_to_w(w_index):
    dict_items=get_ssq_all()
    for key, value in dict_items.items():
        if value == w_index:
            # print(f"键名对应的值是：{w_index}")
            # print(f"键名是：{key}")
            return key
# print(get_ssq_all())

def get_ssq_db():

    # 建立数据库连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='spider_db')

    # 创建游标对象
    cursor = conn.cursor()

    # 执行 SQL 查询语句
    sql = "SELECT kaijianghaoma,w_index,id,qihao FROM ssq_info ORDER BY qihao"
    cursor.execute(sql)

    # 获取查询结果
    result = cursor.fetchall()
    lst=[]
    # i=0
    # w_to_id=get_ssq_all()
    for row in result:
        # i=i+1
        # lst.append(row['kaijianghaoma_red']+row['kaijianghaoma_blue'])
        # w=row[0]+','+row[1]
        # print(row[0]+','+row[1])
        # w=row[0]
        w_index=row[1]
        lst.append(w_index)
        # if i>10:
        #     break

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()
    return lst


def update_ssq_index():
    # 字典集合
    w_to_id=get_ssq_all()

    # 建立数据库连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='spider_db')

    # 创建游标对象
    cursor = conn.cursor()

    # 执行 SQL 查询语句
    sql = "SELECT kaijianghaoma,id,qihao FROM ssq_info"
    cursor.execute(sql)

    # 获取查询结果
    data=[]
    result = cursor.fetchall() 
    for row in result:
        w=row[0]
        w_index=w_to_id[w]

        id=row[1]     
        data.append((w_index,id))
        if len(data)==100:
            cursor.executemany('update ssq_info set w_index=%s where id=%s',data)    
            conn.commit()
            data.clear()
            print("数据库更新进度"+str(id))

    if len(data)>0:
        cursor.executemany('update ssq_info set w_index=%s where id=%s',data)    
        conn.commit()

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()
    print("数据库更新完成")

def get_ssq_lishi_by_qihao(id,size=4):
    # 建立数据库连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='spider_db')
    # 创建游标对象
    cursor = conn.cursor()
    # 执行 SQL 查询语句
    sql = f"SELECT kaijianghaoma,w_index,id FROM ssq_info WHERE qihao < '{id}' ORDER BY qihao DESC LIMIT 4;"
    cursor.execute(sql)
    # 获取查询结果
    result = cursor.fetchall()
    lst=[]
    for row in result:      
        w_index=row[1]
        lst.append(w_index)       
    # 关闭游标和数据库连接
    cursor.close()
    conn.close()
    #重新升序排列
    lst.reverse()
    return lst

# print(get_ssq_db())
# update_ssq_index()
# print(get_ssq_lishi_by_qihao('2023101'))

# print(get_windex_to_w(1464659))
lst=get_ssq_db()
with open('./ssq_db.json','w') as file:
    json.dump(lst, file)